squcsdfandomcom-20200214-history
Transaction
Introduction to Transactions A transaction is a logical, atomic unit of work that contains one or more SQL statements. A transaction groups SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone from the database. Oracle Database assigns every transaction a unique identifier called a transaction ID. All Oracle transactions comply with the basic properties of a database transaction, known as ACID properties. ACID is an acronym for the following: *Atomicity All tasks of a transaction are performed or none of them are. There are no partial transactions. For example, if a transaction starts updating 100 rows, but the system fails after 20 updates, then the database rolls back the changes to these 20 rows. *Consistency The transaction takes the database from one consistent state to another consistent state. For example, in a banking transaction that debits a savings account and credits a checking account, a failure must not cause the database to credit only one account, which would lead to inconsistent data. *Isolation The effect of a transaction is not visible to other transactions until the transaction is committed. For example, one user updating the hr.employees table does not see the uncommitted changes to employees made concurrently by another user. Thus, it appears to users as if transactions are executing serially. *Durability Changes made by committed transactions are permanent. After a transaction completes, the database ensures through its recovery mechanisms that changes from the transaction are not lost. The use of transactions is one of the most important ways that a database management system differs from a file system.http://docs.oracle.com/cd/E11882_01/server.112/e10713/transact.htm Structure of a Transaction A database transaction consists of one or more statements. Specifically, a transaction consists of one of the following: *One or more data manipulation language (DML) statements that together constitute an atomic change to the database *One data definition language (DDL) statement A transaction has a beginning and an end.http://docs.oracle.com/cd/E11882_01/server.112/e10713/transact.htm Beginning of a Transaction A transaction begins when the first executable SQL statement is encountered. An executable SQL statement is a SQL statement that generates calls to a database instance, including DML and DDL statements and the SET TRANSACTION statement. When a transaction begins, Oracle Database assigns the transaction to an available undo data segment to record the undo entries for the new transaction. A transaction ID is not allocated until an undo segment and transaction table slot are allocated, which occurs during the first DML statement. A transaction ID is unique to a transaction and represents the undo segment number, slot, and sequence number.http://docs.oracle.com/cd/E11882_01/server.112/e10713/transact.htm End of a Transaction A transaction ends when any of the following actions occurs: *A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause. In a commit, a user explicitly or implicitly requested that the changes in the transaction be made permanent. Changes made by the transaction are permanent and visible to other users only after a transaction commits. *A user runs a DDL command such as CREATE, DROP, RENAME, or ALTER. The database issues an implicit COMMIT statement before and after every DDL statement. If the current transaction contains DML statements, then Oracle Database first commits the transaction and then runs and commits the DDL statement as a new, single-statement transaction. *A user exits normally from most Oracle Database utilities and tools, causing the current transaction to be implicitly committed. The commit behavior when a user disconnects is application-dependent and configurable. *A client process terminates abnormally, causing the transaction to be implicitly rolled back using metadata stored in the transaction table and the undo segment. After one transaction ends, the next executable SQL statement automatically starts the following transaction. The following example executes an UPDATE to start a transaction, ends the transaction with a ROLLBACK statement, and then executes an UPDATE to start a new transaction .http://docs.oracle.com/cd/E11882_01/server.112/e10713/transact.htm Transaction Names A transaction name is an optional, user-specified tag that serves as a reminder of the work that the transaction is performing. You name a transaction with the SET TRANSACTION ... NAME statement, which if used must be first statement of the transaction. Transaction names provide the following advantages: *It is easier to monitor long-running transactions and to resolve in-doubt distributed transactions. *You can view transaction names along with transaction IDs in applications. For example, a database administrator can view transaction names in Oracle Enterprise Manager (Enterprise Manager) when monitoring system activity. *The database writes transaction names to the transaction auditing redo record, so you can use LogMiner to search for a specific transaction in the redo log. *You can use transaction names to find a specific transaction in data dictionary views such as V$TRANSACTION.http://docs.oracle.com/cd/E11882_01/server.112/e10713/transact.htm Savepoints A savepoint is a user-declared intermediate marker within the context of a transaction. Internally, this marker resolves to an SCN. Savepoints divide a long transaction into smaller parts. If you use savepoints in a long transaction, then you have the option later of rolling back work performed before the current point in the transaction but after a declared savepoint within the transaction. Thus, if you make an error, you do not need to resubmit every statement.http://docs.oracle.com/cd/E11882_01/server.112/e10713/transact.htm Rollback to Savepoint A rollback to a savepoint in an uncommitted transaction means undoing any changes made after the specified savepoint, but it does not mean a rollback of the transaction itself.http://docs.oracle.com/cd/E11882_01/server.112/e10713/transact.htm Enqueued Transactions Depending on the scenario, transactions waiting for previously locked resources may still be blocked after a rollback to savepoint. When a transaction is blocked by another transaction it enqueues on the blocking transaction itself, so that the entire blocking transaction must commit or roll back for the blocked transaction to continue.http://docs.oracle.com/cd/E11882_01/server.112/e10713/transact.htm Committing Transactions A commit ends the current transaction and makes permanent all changes performed in the transaction. When a transaction commits, the following actions occur: *A system change number (SCN) is generated for the COMMIT. The internal transaction table for the associated undo tablespace records that the transaction has committed. The corresponding unique SCN of the transaction is assigned and recorded in the transaction table. See "Serializable Isolation Level". *The log writer (LGWR)process writes remaining redo log entries in the redo log buffers to the online redo log and writes the transaction SCN to the online redo log. This atomic event constitutes the commit of the transaction. *Oracle Database releases locks held on rows and tables. Users who were enqueued waiting on locks held by the uncommitted transaction are allowed to proceed with their work. *Oracle Database deletes savepoints. *Oracle Database performs a commit cleanout. If modified blocks containing data from the committed transaction are still in the SGA, and if no other session is modifying them, then the database removes lock-related transaction information from the blocks. Ideally, the COMMIT cleans out the blocks so that a subsequent SELECT does not have to perform this task. *Oracle Database marks the transaction complete. After a transaction commits, users can view the changes. Typically, a commit is a fast operation, regardless of the transaction size. The speed of a commit does not increase with the size of the data modified in the transaction. The lengthiest part of the commit is the physical disk I/O performed by LGWR. However, the amount of time spent by LGWR is reduced because it has been incrementally writing the contents of the redo log buffer in the background. The default behavior is for LGWR to write redo to the online redo log synchronously and for transactions to wait for the buffered redo to be on disk before returning a commit to the user. However, for lower transaction commit latency, application developers can specify that redo be written asynchronously so that transactions need not wait for the redo to be on disk and can return from the COMMIT call immediately..http://docs.oracle.com/cd/E11882_01/server.112/e10713/transact.htm Referances Category:Database